﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections.Specialized;

namespace ArcSWATMGT
{
    enum TillageType
    {
        Unknown = -1,
        Conservation = 3,
        Conventional = 1,
        Zero = 4
    }

    /// <summary>
    /// Used to assign operation schedules to each HRU. 
    /// 
    /// Should use in following steps
    /// 1. Add manure and non-manure schedule for each crop type in table opSchedules. The fall tillage type is set to conventional tillage. 
    /// 2. Set non-manure schedule to all HRUs beased on landuse
    /// 3. Set manure shedule to all manured HRUs
    /// 4. Change fall tillage type for specific HRUs
    /// </summary>
    class MGT
    {
        private Project _prj;

        public MGT(Project p)
        {
            _prj  = p;
        }

        private static string MGT_SCHEDULE_HRU_TABLE_NAME = "mgt2";     //in project mdb
        private static string MGT_SCHEDULE_TABLE_NAME = "OpSchedules";  //in swat2012.mdb
        private static string[] MGT_FIELDS = new string[] {
            "SUBBASIN","HRU","LANDUSE","SOIL","SLOPE_CD",
            "CROP","YEAR","MONTH","DAY","HUSC","MGT_OP","HEATUNITS",
            "PLANT_ID","CURYR_MAT","LAI_INIT","BIO_INIT","HI_TARG",
            "BIO_TARG","CNOP",
            "IRR_AMT",
            "FERT_ID","FRT_KG","FRT_SURFACE",
            "PEST_ID","PST_KG",
            "TILLAGE_ID","HARVEFF","HI_OVR","GRZ_DAYS",
            "MANURE_ID","BIO_EAT","BIO_TRMP","MANURE_KG",
            "WSTRS_ID","AUTO_WSTRS","AFERT_ID","AUTO_NSTRS",
            "AUTO_NAPP","AUTO_NYR","AUTO_EFF","AFRT_SURFACE",
            "SWEEPEFF","FR_CURB","IMP_TRIG","FERT_DAYS","CFRT_ID",
            "IFRT_FREQ","CFRT_KG","PST_DEP","IHV_GBM","OP_NUM",
            "IRR_SALT","IRR_EFM","IRR_SQ","IRR_EFF",
            "IRR_MX","IRR_ASQ","CPST_ID","PEST_DAYS","IPEST_FREQ",
            "CPST_KG","BURN_FRLB","IRR_SC","IRR_NO","IRR_SCA",
            "IRR_NOA"};
        private static string[] DATE_FIELDS = new string[] { "YEAR", "MONTH", "DAY" };
        private static string[] TEXT_FIELDS = new string[] { "LANDUSE","SOIL","SLOPE_CD",
            "CROP" };

        private string InsertSQL_Format
        {
            get
            {
                StringBuilder sb = new StringBuilder("INSERT INTO mgt2 (");
                for (int i = 0; i < MGT_FIELDS.Length; i++)
                {
                    if(i > 0) sb.Append(",");

                    string field = MGT_FIELDS[i];
                    sb.Append(System.Array.IndexOf(DATE_FIELDS, field) > -1 ? string.Format("[{0}]", field) : field);
                }

                sb.Append(") values (");

                for (int i = 0; i < MGT_FIELDS.Length; i++)
                {
                    if(i > 0) sb.Append(",");

                    string field = MGT_FIELDS[i];
                    sb.Append(System.Array.IndexOf(TEXT_FIELDS, field) > -1 ? "'{" + i.ToString() + "}'" : "{" + i.ToString() + "}");
                }
                sb.Append(")");
                return sb.ToString();                
            }
        }

        /// <summary>
        /// Get the SQL to insert one row to mgt2 table based on information from opSchedules table
        /// </summary>
        /// <param name="mgt">row from mgt2 table used to get HRU information</param>
        /// <param name="schedule">row from opSchedules table used to get schedules</param>
        /// <param name="tillage">Tillage type used to change the tillage type if necessary</param>
        /// <returns></returns>
        private string getInsertSQL(DataRow mgt, DataRow schedule,TillageType tillage)
        {
            string[] values = new string[MGT_FIELDS.Length];

            for (int i = 0; i < MGT_FIELDS.Length; i++)
            {
                string field = MGT_FIELDS[i];

                if (i <= 4) values[i] = mgt[field].ToString(); //HRU nformation, including subbasin,hru,landuse,soil and slope
                else //operations
                {
                    values[i] = "0";                                //default value
                    if (schedule[field] == DBNull.Value) continue;  //do nothing for empty value

                    values[i] = schedule[field].ToString();

                    //change tillage id if necessary
                    if (tillage == TillageType.Unknown) continue;  //don't change tillage
                    if (field.Equals("TILLAGE_ID"))
                    {
                        int till_id = int.Parse(values[i]);
                        if (till_id == 0) continue;             //not tillage operation
                        if (till_id == (int)tillage) continue;  //same tillage, don't need to change

                        //see it's spring or fall tillage
                        bool isFall = false;
                        if (schedule["MONTH"] != DBNull.Value && int.Parse(schedule["MONTH"].ToString()) >= 9)
                            isFall = true;
                        if (!isFall) continue;//not fall tillage, don't need to change                                         
                        
                        //change to given tillage
                        values[i] = ((int)tillage).ToString();

                        //output
                        Console.WriteLine("Change tillage type to " + tillage.ToString());
                    }                  
                }
            }
            return string.Format(InsertSQL_Format,values );
        }

        public void updateMgtSchedule(int[] hrus, string scheduleName)
        {
            foreach (int hru in hrus)
                updateMgtSchedule(hru, scheduleName);
        }

        /// <summary>
        /// Update mgt schedule for given HRU with given schedule. Usually used for manaured HRU.
        /// </summary> 
        /// <param name="hru">The global HRU id, not the id inside each subbasin</param>
        /// <param name="scheduleName">The name of the schedule defined in table OpSchedules</param>
        /// type, only one schedule needs to be used. And this parameter could be used to change the tillage type if necessary.</param>
        /// <remarks>If the given HRU or schedule doesn't exist, nothing will be done.</remarks>
        public void updateMgtSchedule(int hru,string scheduleName)
        {
            //give some message
            Console.WriteLine(string.Format("{0}-{1}", hru, scheduleName));

            //get subbasin, landuse,soil,slope for given hru
            DataTable dt = _prj.GetProjectTable("select * from hrus where hru_id = " + hru.ToString());
            if (dt == null || dt.Rows.Count == 0) { Console.WriteLine("Couldn't find hru " + hru.ToString()); return; }

            //get subbasin,landuse,soil and slope information for this hru
            int subbasin = int.Parse(dt.Rows[0]["SUBBASIN"].ToString());
            string landuse = dt.Rows[0]["LANDUSE"].ToString();
            string soil = dt.Rows[0]["SOIL"].ToString();
            string slope = dt.Rows[0]["SLP"].ToString();
            updateMgtSchedule(subbasin,landuse,soil,slope,scheduleName,TillageType.Unknown);            
        }

        /// <summary>
        /// Update mgt schedule for given HRUs based on given landuse
        /// </summary>
        /// <param name="landuses">A landuse list. All HRUs with this landuse would be set given schedule</param>
        /// <param name="scheduleName">The name of the schedule defined in table OpSchedules</param>
        public void updateMgtSchedule(string[] landuses, string scheduleName)
        {
            foreach (string lu in landuses)
                updateMgtSchedule(lu,scheduleName);
        }

        public void updateMgtSchedule(string landuse, string scheduleName)
        {
            updateMgtSchedule(0, landuse, null, null, scheduleName, TillageType.Unknown);
        }

        /// <summary>
        /// Update mgt schedule for given HRU (landuse, soil and slope) with given schedule
        /// </summary> 
        /// <param name="subbasin">The subbasin id, only used when larger than zero.</param>
        /// <param name="landuse">The landuse of HRU, only used if it's not empty</param>
        /// <param name="soil">The soil of HRU, only used if it's not empty</param>
        /// <param name="slope">The slope of HRU, only used if it's not empty</param>
        /// <param name="scheduleName">The name of the schedule defined in table OpSchedules</param>
        /// <param name="tillage">The type of tillage. If the only difference between two schedules is just the tillage 
        /// type, only one schedule needs to be used. And this parameter could be used to change the tillage type if necessary.</param>
        /// <remarks>If the given HRU or schedule doesn't exist, nothing will be done.</remarks>
        public void updateMgtSchedule(int subbasin, string landuse, string soil, string slope, 
            string scheduleName,TillageType tillage)
        {
            //give some message
            Console.WriteLine(string.Format("{0}-{1}-{2}-{3}-{4}-{5}",subbasin,landuse,soil,slope,scheduleName,tillage));

            //get schedule
            DataTable scheduleTable = _prj.GetSWATTable(string.Format("select * from OpSchedules where SID = '{0}'",scheduleName));
            if (scheduleTable == null || scheduleTable.Rows.Count == 0)
            {
                Console.WriteLine("Couldn't find schedule: " + scheduleName);
                return;
            }

            //sort if necessary
            DataView view = new DataView(scheduleTable);
            view.Sort = "YEAR ASC, OP_NUM ASC, MONTH ASC, DAY ASC";
            scheduleTable = view.ToTable();

            //get all affected hrus
            string sql = getHRUSQL(subbasin,landuse,soil,slope,"hru"); //try to find from hru table, not from mgt2 table because some hru may not be added to the mgt2 table yet.
            DataTable hruTable = _prj.GetProjectTable(sql);
            if (hruTable == null || hruTable.Rows.Count == 0)
            {
                Console.WriteLine("Couldn't find given HRUs: " + sql);
                return;
            }            
            
            //start copy schedule
            foreach (DataRow r in hruTable.Rows)
            {
                //delete first
                string deleteSQL = string.Format("DELETE FROM mgt2 where SUBBASIN={0} and HRU={1}",
                    r["SUBBASIN"],r["HRU"]);
                _prj.ExecuteNonQuery(deleteSQL);

                //add selected schedual
                foreach(DataRow schedule in scheduleTable.Rows)
                {              
                    string insertSQL = getInsertSQL(r,schedule,tillage);
                    _prj.ExecuteNonQuery(insertSQL);
                }
            }
         }

        public void changeFallTillageType(int[] subbasins, string landuse,TillageType tillage)
        {
            foreach (int sub in subbasins)
                changeFallTillageType(sub, landuse,null,null, tillage);
        }

        public void changeFallTillageType(string[] landuses, TillageType tillage)
        {
            foreach (string lu in landuses)
                changeFallTillageType(0, lu, null, null, tillage);
        }

        /// <summary>
        /// Change fall tillage type for given HRUs, i.e. update rows in mgt2 table
        /// </summary>
        /// <param name="subbasin">The subbasin id, only used when larger than zero.</param>
        /// <param name="landuse">The landuse of HRU, only used if it's not empty</param>
        /// <param name="soil">The soil of HRU, only used if it's not empty</param>
        /// <param name="slope">The slope of HRU, only used if it's not empty</param>
        /// <param name="tillage">The type of tillage to be used. If it's same as the current one, do nothing.</param>
        /// <remarks>This should be the last step in operation schedule setup</remarks>
        public void changeFallTillageType(int subbasin, string landuse, string soil, string slope,TillageType tillage)
        {
            string condition = getCondition(subbasin, landuse, soil, slope);

            //give some information
            Console.WriteLine("Change tillage type to " + tillage.ToString() + " for HRUs: "  + condition);

            //don't work for unknown type
            if (tillage == TillageType.Unknown) return;

            //get all affected hrus            
            string sql = "select * from mgt2 where MGT_OP = 6";
            if (!string.IsNullOrEmpty(condition) && condition.Length > 0)
                sql += " AND " + condition;

            DataTable mgtTable = _prj.GetProjectTable(sql);
            if (mgtTable == null || mgtTable.Rows.Count == 0) 
            {
                Console.WriteLine("No tillage operation for HRUs: " + sql);
                return;
            }

            //start update schedule
            foreach (DataRow r in mgtTable.Rows)
            {
                int till_id = int.Parse(r["TILLAGE_ID"].ToString());
                if (till_id == 0) continue;             //not tillage operation
                if (till_id == (int)tillage) continue;  //same tillage, don't need to change

                //see it's spring or fall tillage
                bool isFall = false;
                if (r["MONTH"] != DBNull.Value && int.Parse(r["MONTH"].ToString()) >= 9)
                    isFall = true;
                if (!isFall) continue;//not fall tillage, don't need to change                                         

                //change to given tillage
                string updateSQL = string.Format("UPDATE mgt2 set TILLAGE_ID = {0} where SUBBASIN={1} AND HRU={2} AND [YEAR] = {3} AND [MONTH] = {4} AND [DAY] = {5} AND MGT_OP = 6",
                    (int)tillage, r["SUBBASIN"], r["HRU"], r["YEAR"], r["MONTH"], r["DAY"]);
                _prj.ExecuteNonQuery(updateSQL);               
            }
        }

        private string getHRUSQL(int subbasin, string landuse, string soil, string slope,string tableName)
        {
            string condition = getCondition(subbasin, landuse, soil, slope);
            string sql = "select distinct SUBBASIN, HRU,LANDUSE,SOIL,SLOPE_CD from " + tableName;
            if (!string.IsNullOrEmpty(condition)) sql += " where " + condition;
            return sql;
        }

        private string getCondition(int subbasin, string landuse, string soil, string slope)
        {
            StringBuilder sb = new StringBuilder();
            if (subbasin > 0) sb.Append(string.Format("SUBBASIN = {0}", subbasin));
            if (!string.IsNullOrEmpty(landuse))
            {
                if (sb.Length > 0) sb.Append(" AND ");
                sb.Append(string.Format("LANDUSE = '{0}'", landuse));
            }
            if (!string.IsNullOrEmpty(soil))
            {
                if (sb.Length > 0) sb.Append(" AND ");
                sb.Append(string.Format("SLOPE_CD = '{0}'", slope));
            }
            return sb.ToString();
        }

        /// <summary>
        /// Set CNOP for plant/harvest/kill/tillage operation. 
        /// The default value is 0, which means the initial CN2 would be used during the simulaion.
        /// 
        /// For plant operation, the CNOP depends on crop and soil
        /// For tillage operation, a value will be multipled to previous CNOP
        /// 1. Generic Fall Plowing Operation, 1.075 (this is the conventional till)
        /// 2. Generic Spring Plowing Operation, 1.025
        /// 3. Generic Conservation Tillage, 1.025 (this is the conservation till)
        /// 4. Generic No-till Mixing, 1.0
        /// For harvest/kill operation, it's assumed as bare soil, A77,B86, C91, D94
        /// </summary>
        public void changeCNOP()
        {
            //get all hrus
            DataTable hruTable = _prj.GetProjectTable("select SUBBASIN,HRU,SOIL from hru");
            if (hruTable == null || hruTable.Rows.Count == 0) return;

            //start update schedule
            foreach (DataRow hruRow in hruTable.Rows)
            {
                string subbasin = hruRow["SUBBASIN"].ToString();
                string hru = hruRow["HRU"].ToString();
                string soil = hruRow["SOIL"].ToString();

                //get corresponding plant/harvest/tillage operation
                DataTable mgtTable = _prj.GetProjectTable(
                    "select * from mgt2 where (MGT_OP = 1 OR MGT_OP = 5 OR MGT_OP = 6) AND [YEAR] > 0 AND [MONTH] > 0 AND [DAY]>0 AND SUBBASIN = " + 
                    subbasin + " AND HRU = " + hru);

                if (mgtTable == null || mgtTable.Rows.Count == 0) 
                    continue;

                //sort by date
                DataView view = new DataView(mgtTable);
                view.Sort = "YEAR ASC, MONTH ASC, DAY ASC, MGT_OP ASC";
                mgtTable = view.ToTable();

                //get hydrological group form usersoil table
                DataTable usersoilTable = _prj.GetSWATTable("select HYDGRP from usersoil where SNAM = '" + soil + "'");
                if (usersoilTable == null || usersoilTable.Rows.Count == 0) 
                    continue;
                string hydroGroup = usersoilTable.Rows[0][0].ToString();

                //get default CN2 from mgt1
                DataTable mgt1Table = _prj.GetProjectTable("select CN2 from mgt1 where SUBBASIN = " + 
                    subbasin + " AND HRU = " + hru);
                if(mgt1Table == null || mgt1Table.Rows.Count == 0) 
                    continue;
                double defaultCN2 = double.Parse(mgt1Table.Rows[0]["CN2"].ToString());
                double currentCN2 = defaultCN2;

                //start to change CNOP
                foreach(DataRow cnopRow in mgtTable.Rows)
                {
                    int op_id = int.Parse(cnopRow["MGT_OP"].ToString());

                    if (op_id == 1) currentCN2 = defaultCN2; //plant
                    else if (op_id == 5)//harvest/kill
                    {
                        if (hydroGroup.Equals("A")) currentCN2 = 77;
                        else if (hydroGroup.Equals("B")) currentCN2 = 86;
                        else if (hydroGroup.Equals("C")) currentCN2 = 91;
                        else if (hydroGroup.Equals("D")) currentCN2 = 94;
                    }
                    else//tillage
                    {
                        int till_id = int.Parse(cnopRow["TILLAGE_ID"].ToString());
                        if (till_id == 1) currentCN2 *= 1.075;
                        else if (till_id == 2) currentCN2 *= 1.025;
                        else if (till_id == 3) currentCN2 *= 1.025;
                        else if (till_id == 4) currentCN2 *= 1.0;
                    }
                    if (currentCN2 > 98) currentCN2 = 98;
                    if (currentCN2 < 35) currentCN2 = 35;

                    //change to given tillage
                    string updateSQL = string.Format("UPDATE mgt2 set CNOP = {0} where SUBBASIN={1} AND HRU={2} AND [YEAR] = {3} AND [MONTH] = {4} AND [DAY] = {5} AND MGT_OP = {6}",
                        currentCN2, cnopRow["SUBBASIN"], cnopRow["HRU"], cnopRow["YEAR"], cnopRow["MONTH"], cnopRow["DAY"], op_id);
                    _prj.ExecuteNonQuery(updateSQL);
                }
            }
        }

        /// <summary>
        /// Validate NROT and HUSC in MGT1 table based on MGT2 data. The default value of NROT is 1 and 0 for HUSC, i.e 1 year rotation and using heat unit.
        /// Their values needs to be modified based on real mgt2 data        /// 
        /// </summary>
        public void validateMGT1()
        {
            //get all hrus
            DataTable mgt1Table = _prj.GetProjectTable("select SUBBASIN,HRU,NROT,HUSC from mgt1");
            if (mgt1Table == null || mgt1Table.Rows.Count == 0) return;

            //start update schedule
            foreach (DataRow mgt1Row in mgt1Table.Rows)
            {
                string subbasin = mgt1Row["SUBBASIN"].ToString();
                string hru = mgt1Row["HRU"].ToString();

                //check number of years of rotation
                int nrot = Convert.ToInt32(mgt1Row["NROT"].ToString());
                DataTable mgt2Table = _prj.GetProjectTable(
                    "select max([YEAR]) from mgt2 where SUBBASIN = " +
                    subbasin + " AND HRU = " + hru);
                if (mgt2Table == null || mgt2Table.Rows.Count == 0)
                    continue;
                int nrot_mgt2 = Convert.ToInt32(mgt2Table.Rows[0][0].ToString());
                if (nrot_mgt2 != nrot)
                {
                    //update NROT
                    string updateSQL = string.Format("UPDATE mgt1 set NROT = {0} where SUBBASIN={1} AND HRU={2}",
                        nrot_mgt2,subbasin,hru);
                    _prj.ExecuteNonQuery(updateSQL);
                    Console.WriteLine(string.Format("Update NROT to {0} for subbasin {1} HRU {2}", nrot_mgt2,subbasin,hru));
                }

                //check HUSC flag, if MONTH and DAY is not 0, should set HUSC as 1
                int husc = Convert.ToInt32(mgt1Row["HUSC"].ToString());
                mgt2Table = _prj.GetProjectTable(
                    "select max([MONTH]) from mgt2 where SUBBASIN = " +
                    subbasin + " AND HRU = " + hru);
                if (mgt2Table == null || mgt2Table.Rows.Count == 0)
                    continue;

                int max_month = 0;                
                if(!(mgt2Table.Rows[0][0] is System.DBNull))
                    max_month = Convert.ToInt32(mgt2Table.Rows[0][0].ToString());
                if (max_month > 0 && husc == 0)
                {
                    //update NROT
                    string updateSQL = string.Format("UPDATE mgt1 set HUSC = 1 where SUBBASIN={1} AND HRU={2}",
                        nrot_mgt2, subbasin, hru);
                    _prj.ExecuteNonQuery(updateSQL);
                    Console.WriteLine(string.Format("Update HUSC to 1 for subbasin {0} HRU {1}", subbasin, hru));

                }
                if (max_month == 0 && husc == 1)
                {
                    //update NROT
                    string updateSQL = string.Format("UPDATE mgt1 set HUSC = 0 where SUBBASIN={1} AND HRU={2}",
                        nrot_mgt2, subbasin, hru);
                    _prj.ExecuteNonQuery(updateSQL);
                    Console.WriteLine(string.Format("Update HUSC to 0 for subbasin {0} HRU {1}", subbasin, hru));
                }
                
            }
        }
    }
}
